﻿CREATE PROCEDURE [steve].[spSteve_ProcessImport]
	@count int = 10000
AS
	declare @SystemName varchar(50)
		, @ExternalId varchar(100)
		, @CreateDate datetime2(7)
		, @CreateMembershipId uniqueidentifier
		, @Language varchar(10)
		, @Path varchar(255)
		, @ContentTypeName varchar(50)
		, @ContentData varbinary(max)
		, @Title nvarchar(200)
		, @Teaser nvarchar(4000)
		, @PublicationDate datetime2(7)
		, @PublisherUrlAuthority varchar(255)
		, @PublisherUrl varchar(800)
		, @Publisher nvarchar(200)
		, @Author nvarchar(200)
		, @Height int
		, @Width int
		, @MimeType varchar(50)
		, @MimeSubtype varchar(50)
		, @RevisionDate datetime2(7)
		, @RevisionMembershipId uniqueidentifier
		, @ContentId uniqueidentifier
		, @ContentDataId uniqueidentifier
		, @PublisherUrlId uniqueidentifier
		, @OriginalUrl1Authority varchar(255)
		, @OriginalUrl1 varchar(800)
		, @SiteName1 varchar(50)
		, @SiteUrlId1 uniqueidentifier
		, @OriginalUrl2Authority varchar(255)
		, @OriginalUrl2 varchar(800)
		, @SiteName2 varchar(50)
		, @OriginalUrl3Authority varchar(255)
		, @OriginalUrl3 varchar(800)
		, @SiteName3 varchar(50)


		, @now datetime2(7)
		, @error int
		, @errorMessage varchar(500)
		, @siteName varchar(50)
		, @rc int
		, @urlRewriteId uniqueidentifier
		, @ignoreQueryString bit

	declare @dummyTable table (LatestRevisionNumber int null)

	select @now = GETDATE()

	begin transaction

	declare importCursor cursor local fast_forward for
		select top (@count) i.SystemName
			, i.ExternalId
			, i.CreateDate
			, i.CreateMembershipId
			, i.[Language]
			, i.[Path]
			, i.ContentTypeName
			, ISNULL(i.CleanContentData, i.OriginalContentData) as ContentData
			, ISNULL(i.CleanTitle, i.OriginalTitle) as Title
			, ISNULL(i.CleanTeaser, i.OriginalTeaser) as Teaser
			, i.PublicationDate
			, i.PublisherUrlAuthority
			, i.PublisherUrl
			, i.Publisher
			, i.Author
			, i.Height
			, i.Width
			, i.MimeType
			, i.MimeSubtype
			, i.RevisionDate
			, i.RevisionMembershipId
			, c.ContentId
			, i.OriginalUrl1Authority
			, i.OriginalUrl1
			, s.SiteName
			, u.SiteUrlId
			, i.OriginalUrl2Authority
			, i.OriginalUrl2
			, s2.SiteName
			, i.OriginalUrl3Authority
			, i.OriginalUrl3
			, s3.SiteName		
		from steve.ContentImportStaging i
			left outer join steve.uvSteve_ContentRevision c on c.SystemName = i.SystemName and c.ExternalId = i.ExternalId and c.LatestRevisionNumber = c.RevisionNumber
			left outer join steve.[Site] s on s.DefaultAuthority = i.OriginalUrl1Authority
			left outer join steve.[SiteUrl] u on u.SiteName = s.SiteName and u.Url = i.OriginalUrl1
			left outer join steve.[Site] s2 on s2.DefaultAuthority = i.OriginalUrl2Authority
			left outer join steve.[Site] s3 on s3.DefaultAuthority = i.OriginalUrl3Authority
		where ReadyToProcess = 1
			and Processed is null
			and (c.ContentId is null or c.RevisionDate < i.RevisionDate)

	select @error = @@ERROR
	if @error <> 0 goto CLEANUP

	open importCursor

	select @error = @@ERROR
	if @error <> 0 goto CLEANUP

	fetch next from importCursor into @SystemName
		, @ExternalId
		, @CreateDate
		, @CreateMembershipId
		, @Language
		, @Path
		, @ContentTypeName
		, @ContentData
		, @Title
		, @Teaser
		, @PublicationDate
		, @PublisherUrlAuthority
		, @PublisherUrl
		, @Publisher
		, @Author
		, @Height
		, @Width
		, @MimeType
		, @MimeSubtype
		, @RevisionDate
		, @RevisionMembershipId
		, @ContentId
		, @OriginalUrl1Authority
		, @OriginalUrl1
		, @SiteName1
		, @SiteUrlId1
		, @OriginalUrl2Authority
		, @OriginalUrl2
		, @SiteName2
		, @OriginalUrl3Authority
		, @OriginalUrl3
		, @SiteName3

	select @error = @@ERROR
	if @error <> 0 goto CLEANUP

	while @@FETCH_STATUS = 0
	begin
		select @ContentDataId = NEWID()

		insert into ContentData (ContentDataId, CreateDate, ContentData) values (@ContentDataId, @RevisionDate, @ContentData)

		select @error = @@ERROR
		if @error <> 0 goto CLEANUP

		--site url 1
		if @SiteUrlId1 is null and @OriginalUrl1 is not null
		begin
			if @SiteName1 is null
			begin
				select @errorMessage = 'Site ' + @OriginalUrl1Authority + ' is not setup in Steve.'
				raiserror (@errorMessage, 16, 1)
				select @error = 50000
				goto CLEANUP
			end

			select @SiteUrlId1 = NEWID()

			select @error = @@ERROR
			if @error <> 0 goto CLEANUP

			select @ignoreQueryString = case when charindex('?', @OriginalUrl1, 1) > 0 then 0 else 1 end

			select @error = @@ERROR
			if @error <> 0 goto CLEANUP

			insert into @dummyTable
			exec @error = steve.spSteve_InsertSiteUrl @SiteUrlId1, @SiteName1, null, null, @OriginalUrl1, null, null, 0
				, @ignoreQueryString, null, null, null, @now, null, 'Created by import utility during content import' 

			select @error = coalesce(nullif(@error, 0), @@ERROR)
			if @error <> 0 goto CLEANUP
		end
		
		--site url 2 (first rewrite)
		if @OriginalUrl2 is not null
		begin
			if @SiteName2 is null
			begin
				select @errorMessage = 'Site ' + @OriginalUrl2Authority + ' is not setup in Steve.'
				raiserror (@errorMessage, 16, 1)
				select @error = 50000
				goto CLEANUP
			end

			if not exists(select 1 from steve.UrlRewrite where Url = @OriginalUrl2)
			begin
				select @urlRewriteId = NEWID()

				select @error = @@ERROR
				if @error <> 0 goto CLEANUP

				select @ignoreQueryString = case when charindex('?', @OriginalUrl2, 1) > 0 then 0 else 1 end

				select @error = @@ERROR
				if @error <> 0 goto CLEANUP

				insert into @dummyTable
				exec @error = steve.spSteve_InsertUrlRewrite @urlRewriteId, @SiteName2, null, null, @OriginalUrl2
					, null, null, 0, 0, @ignoreQueryString, 1, @SiteUrlId1, @now, null, 'Created by import utility during content import'

				select @error = coalesce(nullif(@error, 0), @@ERROR)
				if @error <> 0 goto CLEANUP
			end
		end

		--site url 3 (second rewrite)
		if @OriginalUrl3 is not null
		begin
			if @SiteName3 is null
			begin
				select @errorMessage = 'Site ' + @OriginalUrl3Authority + ' is not setup in Steve.'
				raiserror (@errorMessage, 16, 1)
				select @error = 50000
				goto CLEANUP
			end

			if not exists(select 1 from steve.UrlRewrite where Url = @OriginalUrl3)
			begin
				select @urlRewriteId = NEWID()

				select @error = @@ERROR
				if @error <> 0 goto CLEANUP

				select @ignoreQueryString = case when charindex('?', @OriginalUrl3, 1) > 0 then 0 else 1 end

				select @error = @@ERROR
				if @error <> 0 goto CLEANUP

				insert into @dummyTable
				exec @error = steve.spSteve_InsertUrlRewrite @urlRewriteId, @SiteName3, null, null, @OriginalUrl3
					, null, null, 0, 0, @ignoreQueryString, 1, @SiteUrlId1, @now, null, 'Created by import utility during content import'

				select @error = coalesce(nullif(@error, 0), @@ERROR)
				if @error <> 0 goto CLEANUP
			end
		end
		
		--todo: figure out publisher url id...

		if @ContentId is null
		begin
			--new content
			select @ContentId = NEWID()

			insert into @dummyTable
			exec @error = steve.spSteve_InsertContent @ContentId, @ContentTypeName, @Path, @Language, @SystemName, @ExternalId
				, @SiteUrlId1, @Title, @Teaser, null, @PublicationDate, @PublisherUrlId, @Publisher, @Author, @Height, @Width
				, @MimeType, @MimeSubtype, @ContentDataId, @RevisionDate, @RevisionMembershipId, N'Created by import utility'

			select @error = coalesce(nullif(@error, 0), @@ERROR)
			if @error <> 0 goto CLEANUP
		end
		else
		begin
			--existing content

			insert into @dummyTable
			exec @error = steve.spSteve_UpdateContent @ContentId, @Path, @SiteUrlId1, @Title, @Teaser, null, @PublicationDate, @PublisherUrlId
				, @Publisher, @Author, @Height, @Width, @MimeType, @MimeSubtype, @ContentDataId, @RevisionDate, @RevisionMembershipId, N'Updated by import utility'

			select @error = coalesce(nullif(@error, 0), @@ERROR)
			if @error <> 0 goto CLEANUP
		end

		--do usercontent (add and update only, no delete)
		insert into UserContent (MembershipId, ContentId, ReadDate, Rating, RatingDate)
		select import.MembershipId
			, @ContentId
			, import.ReadDate
			, import.Rating
			, import.RatingDate
		from UserContentImportStaging import
			left outer join UserContent existing on existing.ContentId = @ContentId
				and existing.MembershipId = import.MembershipId
		where import.ExternalId = @ExternalId
			and import.SystemName = @SystemName
			and existing.ContentId is null

		select @error = @@ERROR
		if @error <> 0 goto CLEANUP

		update existing
		set Rating = ISNULL(existing.Rating, import.Rating)
			, RatingDate = ISNULL(existing.RatingDate, import.RatingDate)
		from UserContentImportStaging import
			inner join UserContent existing on existing.ContentId = @ContentId
				and existing.MembershipId = import.MembershipId
		where import.ExternalId = @ExternalId
			and import.SystemName = @SystemName

		select @error = @@ERROR
		if @error <> 0 goto CLEANUP

		--categories (additive only, no delete)
		insert into Category (CategoryName, CategoryDescription, RelevancyWeight, IsUserTag, IsSiteVisible, CreateDate, UpdateDate)
		select stage.CategoryName
			, stage.CategoryDescription
			, 1.0
			, 0
			, 1
			, @now
			, @now
		from ContentCategoryImportStaging stage
			left join Category on Category.CategoryName = stage.CategoryName
		where Category.CategoryName is null 

		select @error = @@ERROR
		if @error <> 0 goto CLEANUP

		insert into ContentCategory (ContentId, CategoryName, CreateDate)
		select @ContentId
			, stage.CategoryName
			, @now
		from ContentCategoryImportStaging stage
			left outer join ContentCategory cc on cc.ContentId = @ContentId
				and cc.CategoryName = stage.CategoryName
		where cc.ContentId is null

		select @error = @@ERROR, @rc = @@ROWCOUNT
		if @error <> 0 goto CLEANUP

		if @rc > 0
		begin
			exec @error = [steve].[spSteve_RebuildContentSimilarityCache] @IncrementContentId = @ContentId

			select @error = coalesce(nullif(@error, 0), @@ERROR)
			if @error <> 0 goto CLEANUP
		end

		--flag as complete
		update ContentImportStaging set Processed = @now where SystemName = @SystemName and ExternalId = @ExternalId

		select @error = @@ERROR
		if @error <> 0 goto CLEANUP

		fetch next from importCursor into @SystemName
			, @ExternalId
			, @CreateDate
			, @CreateMembershipId
			, @Language
			, @Path
			, @ContentTypeName
			, @ContentData
			, @Title
			, @Teaser
			, @PublicationDate
			, @PublisherUrlAuthority
			, @PublisherUrl
			, @Publisher
			, @Author
			, @Height
			, @Width
			, @MimeType
			, @MimeSubtype
			, @RevisionDate
			, @RevisionMembershipId
			, @ContentId
			, @OriginalUrl1Authority
			, @OriginalUrl1
			, @SiteName1
			, @SiteUrlId1
			, @OriginalUrl2Authority
			, @OriginalUrl2
			, @SiteName2
			, @OriginalUrl3Authority
			, @OriginalUrl3
			, @SiteName3

		select @error = @@ERROR
		if @error <> 0 goto CLEANUP
	end

CLEANUP:
	close importCursor
	deallocate importCursor

	if @error <> 0
	begin
		rollback transaction
		return @error
	end

	commit transaction
	
	return 0